/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.catalogos.prueba.dao;

import com.catalogos.prueba.entities.AtributoCatalogo;
import com.catalogos.prueba.entities.Catalogo;
import com.catalogos.prueba.entities.ValorCatalogo;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author 874836
 */
public class ConsultasDAO {


    public List<Catalogo> getCatalogo()throws SQLException, Exception{
        List<Catalogo> listCat=new ArrayList<Catalogo>();
        Conexion cn=new Conexion();
        ResultSet rs=null;
        String query="SELECT ID, NOMBRE FROM CATALOGO";
        try{
        rs=cn.getStatement(query);

        while(rs.next()){
          Catalogo cat=new Catalogo();
          cat.setId(rs.getInt(1));
          cat.setNombre(rs.getString(2));
          listCat.add(cat);
        }

        } catch (SQLException e) {
            throw new SQLException("getCatalogo:SQLException[" + e.getMessage() + "]", e);
        }catch(Exception e){
              throw new Exception("getCatalogo:Exception[" + e.getMessage() + "]", e);
        }
        return listCat;
    }


    public List<AtributoCatalogo> getAtributoCat(int idCat) throws SQLException, Exception {
        List<AtributoCatalogo> listAtCat = new ArrayList<AtributoCatalogo>();
        Conexion cn = new Conexion();
        PreparedStatement pst = null;
        ResultSet rs = null;
        String query = "SELECT CAT.ID, AC.ATRIBUTO, AC.ID_CAT, AC.IND_COL,  FROM ATRIBUTO_CAT AC, CATALOGO CAT WHERE CAT.ID_CAT=?";
        try {
            pst = cn.getPreparedStatement(query);
            pst.setInt(1, idCat);
            rs = pst.executeQuery();
            
            while (rs.next()) {
                AtributoCatalogo atCat = new AtributoCatalogo();
                atCat.setId(rs.getInt(1));
                atCat.setAtributo(rs.getString(2));
                atCat.setIndColumna(rs.getInt(4));

                listAtCat.add(atCat);
            }

        } catch (SQLException e) {
            throw new SQLException("getAtributoCat:SQLException[" + e.getMessage() + "]", e);
        } catch (Exception e) {
            throw new Exception("getAtributoCat:Exception[" + e.getMessage() + "]", e);
        }
        return listAtCat;

    }

    public List<ValorCatalogo> getValorCatalogo(int idCat)throws SQLException, Exception{
        List<ValorCatalogo> listValCat=new ArrayList<ValorCatalogo>();
        PreparedStatement pst = null;
        Conexion cn=new Conexion();
        ResultSet rs=null;
        String query=""
        		
        		//Catalogo
        		+ "SELECT C.ID, "
        		+ "C.NOMBRE, "
        		
        		//Atributos
        		+ "AC.ID ID_ATRIBUTO, "
        		+ "AC.IND_COL, "
        		+ "AC.ATRIBUTO, "
        		
        		//Valores
        		+ "V.ID ID_VALOR_CATALOGO, "
        		+ "V.VALOR, "
        		+ "V.IND_REG "
        		+ "FROM VALOR_CATALOGO V, "
        		+ "ATRIBUTO_CAT AC, "
        		+ "CATALOGO C "
        		+ "WHERE AC.ID=V.ID_ATRIB_CAT "
        		+ "AND C.ID=AC.ID_CAT "
        		+ " AND C.ID=?";
        try{
            pst = cn.getPreparedStatement(query);
            pst.setInt(1, idCat);
            rs = pst.executeQuery();

        while(rs.next()){
            ValorCatalogo valCat=new ValorCatalogo();
            
            Catalogo catalogo = new Catalogo();
            catalogo.setId(rs.getInt(1));
            catalogo.setNombre(rs.getString(2));
            
            
            AtributoCatalogo atributoCatalogo = new AtributoCatalogo();
            atributoCatalogo.setId(rs.getInt(3));
            atributoCatalogo.setIndColumna(rs.getInt(4));
            atributoCatalogo.setAtributo(rs.getString(5));
            atributoCatalogo.setCatalogo(catalogo);
            
            valCat.setAtributosCatalogo(atributoCatalogo);
            valCat.setId(rs.getInt(6));
            valCat.setValor(rs.getString(7));
            valCat.setAtributosCatalogo(atributoCatalogo);
            valCat.setIndReg(rs.getInt(8));

            listValCat.add(valCat);
        }
        } catch (SQLException e) {
            throw new SQLException("getValorCatalogo:SQLException[" + e.getMessage() + "]", e);
        } catch (Exception e) {
            throw new Exception("getValorCatalogo:Exception[" + e.getMessage() + "]", e);
        }
      return listValCat;
    }



}
